package cn.uncode.baas.server.service.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import cn.uncode.dal.utils.JsonUtils;
import cn.uncode.baas.server.cache.SystemCache;
import cn.uncode.baas.server.dto.RestAdmin;
import cn.uncode.baas.server.dto.RestApp;
import cn.uncode.baas.server.dto.RestField;
import cn.uncode.baas.server.dto.RestGroup;
import cn.uncode.baas.server.dto.RestMethod;
import cn.uncode.baas.server.dto.RestRole;
import cn.uncode.baas.server.dto.RestTable;
import cn.uncode.baas.server.dto.RestUser;
import cn.uncode.baas.server.dto.RestUserAcl;
import cn.uncode.baas.server.service.IResterService;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

@Service
public class ResterService extends JdbcDaoSupport implements IResterService {
	
	private static final Logger LOG = Logger.getLogger(ResterService.class);
	
	private static final String SQL_SELECT_REST_APP = "select * from restapp where bucket = ?";
	private static final String SQL_SELECT_REST_METHOD = "select * from restmethod where bucket = ? and `name` = ? and `option` = ? and `version` = ?";
	private static final String SQL_SELECT_REST_FIELD = "select * from restfield where bucket = ? and methodId = ?";
	private static final String SQL_SELECT_REST_TABLE = "select * from resttable where bucket = ? and `name` = ?";
	private static final String SQL_SELECT_REST_TABLES = "select * from resttable where bucket = ?";
	private static final String SQL_SELECT_REST_USER = "select * from restuser where bucket = ?";
	private static final String SQL_SELECT_REST_USER_INSERT = "insert into restuser(bucket,tableName,usernameField,passwordField,statusField,emailAuth,defaultGroup,emailField,mobileField)values(?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String SQL_SELECT_REST_USER_UPDATE_BY_ID = "update restuseracl set tableName = ?,usernameField = ?,passwordField = ?,statusField = ?,emailAuth = ?,defaultGroup = ?,emailField = ?,mobileField = ? where id = ?";
	private static final String SQL_SELECT_REST_USER_UPDATE_BY_NAME = "update restuseracl set tableName = ?,usernameField = ?,passwordField = ?,statusField = ?,emailAuth = ?,defaultGroup = ?,emailField = ?,mobileField = ? where bucket = ?";
	private static final String SQL_SELECT_REST_ADMIN = "select * from restadmin where bucket = ? and tableName = ?";
	private static final String SQL_SELECT_REST_USER_ACL = "select * from restuseracl where bucket = ? and username = ?";
	private static final String SQL_SELECT_REST_USER_ACL_INSERT = "insert into restuseracl(bucket, username, groups, roles)values(?, ?, ?, ?)";
	private static final String SQL_SELECT_REST_USER_ACL_UPDATE = "update restuseracl set groups = ?, roles = ? where bucket = ? and username = ?";
	private static final String SQL_SELECT_REST_USER_ACL_DELETE = "delete from restuseracl where bucket = ? and username = ?";
	private static final String SQL_SELECT_REST_USER_ACL_DELETE_ID = "delete from restuseracl where id = ?";
	private static final String SQL_SELECT_REST_USER_ACL_COUNT = "select count(1) from restuseracl where bucket = ? and username = ?";
	private static final String SQL_SELECT_REST_GROUP = "select * from restgroup where bucket = ? and `name` = ?";
	private static final String SQL_SELECT_REST_GROUP_INSERT = "insert into restgroup(bucket, `name`, `desc`, roles)values(?, ?, ?, ?)";
	private static final String SQL_SELECT_REST_GROUP_UPDATE = "update restgroup set `desc` = ?, roles = ? where bucket = ? and `name` = ?";
	private static final String SQL_SELECT_REST_GROUP_DELETE = "delete from restgroup where bucket = ? and `name` = ?";
	private static final String SQL_SELECT_REST_GROUP_DELETE_ID = "delete from restgroup where id= ?";
	private static final String SQL_SELECT_REST_GROUP_COUNT = "select count(1) from restgroup where bucket = ? and `name` = ?";
	private static final String SQL_SELECT_REST_GROUP_LIST = "select * from restgroup where bucket = ? and ( 1=1 ";
	private static final String SQL_SELECT_REST_ROLE = "select * from restrole where bucket = ? and `name` = ?";
	private static final String SQL_SELECT_REST_ROLE_LIST = "select * from restrole where bucket = ? and ( 1=1 ";
	private static final String SQL_SELECT_REST_ROLE_INSERT = "insert into restrole(bucket, `name`, `desc`)values(?, ?, ?)";
	private static final String SQL_SELECT_REST_ROLE_UPDATE = "update restrole set `desc` = ? where bucket = ? and `name` = ?";
	private static final String SQL_SELECT_REST_ROLE_DELETE = "delete from restrole where bucket = ? and `name` = ?";
	private static final String SQL_SELECT_REST_ROLE_DELETE_ID = "delete from restrole where id = ?";
	
	@Override
	public RestApp loadRestApp(String bucket) {
		Map<String, Object> map = null;
		try{
			map = getJdbcTemplate().queryForMap(SQL_SELECT_REST_APP, new Object[]{bucket});
		}catch (Exception e){
			LOG.debug("load rest app error", e);
		}
		RestApp restApp = JsonUtils.mapToObj(map, RestApp.class);
		if(null != restApp){
			SystemCache.setRestApp(restApp);
		}
		return restApp;
	}

	@Override
	public RestMethod loadRestMethod(String bucket, String name, String option, String version) {
		//rest method
		Map<String, Object> map = null;
		try{
			map = getJdbcTemplate().queryForMap(SQL_SELECT_REST_METHOD, new Object[]{bucket, name, option, version});
		}catch (Exception e){
			LOG.debug("load rest method error", e);
		}
		RestMethod restMethod = null;
		if (map != null) {
			restMethod = JsonUtils.mapToObj(map, RestMethod.class);
			//rest field
			List<Map<String, Object>> maps = null;
			try{
				maps = getJdbcTemplate().queryForList(SQL_SELECT_REST_FIELD, new Object[]{bucket, restMethod.getId()});
			}catch (Exception e){
				LOG.debug("load rest field error", e);
			}
			for(Map<String, Object> field:maps){
				RestField restField = JsonUtils.mapToObj(field, RestField.class);
				restMethod.addRestField(restField);
			}
			//store
			if(null != restMethod){
				SystemCache.setRestMethod(restMethod);
			}
		}
		return restMethod;
	}

	@Override
	public RestTable loadRestTable(String bucket, String name) {
		//rest table
		Map<String, Object> map = null;
		try{
			map = getJdbcTemplate().queryForMap(SQL_SELECT_REST_TABLE, new Object[]{bucket, name});
		}catch (Exception e){
			LOG.debug("load rest table error", e);
		}
		RestTable restTable = JsonUtils.mapToObj(map, RestTable.class);
		if(null != restTable){
			SystemCache.setRestTable(restTable);
		}
		return restTable;
	}

	@Override
	public RestUser loadRestUser(String bucket) {
		//rest user
		Map<String, Object> map = null;
		try{
			map = getJdbcTemplate().queryForMap(SQL_SELECT_REST_USER, new Object[]{bucket});
		}catch (Exception e){
			LOG.debug("load rest user error", e);
		}
		RestUser restUser = JsonUtils.mapToObj(map, RestUser.class);
		if(null != restUser){
			SystemCache.setRestUser(restUser);
		}
		return restUser;
	}

	@Override
	public List<RestAdmin> loadRestAdmin(String bucket, String table) {
		List<Map<String, Object>> maps = null;
		try{
			maps = getJdbcTemplate().queryForList(SQL_SELECT_REST_ADMIN, new Object[]{bucket, table});
		}catch (Exception e){
			LOG.debug("load rest admin error", e);
		}
		List<RestAdmin> list = new ArrayList<RestAdmin>();
		for(Map<String, Object> field:maps){
			RestAdmin restAdmin = JsonUtils.mapToObj(field, RestAdmin.class);
			list.add(restAdmin);
		}
		if(null != list){
			SystemCache.setRestAdmin(bucket, table, list);
		}
		return list;
	}
	
	
	public RestUserAcl loadRestUserAcl(String bucket, String name){
		Map<String, Object> map = null;
		try{
			map = getJdbcTemplate().queryForMap(SQL_SELECT_REST_USER_ACL, new Object[]{bucket, name});
		}catch (Exception e){
			LOG.debug("load rest usr acl error", e);
		}
		RestUserAcl restUserAcl = JsonUtils.mapToObj(map, RestUserAcl.class);
		return restUserAcl;
	}
	
	
	public RestGroup loadRestGroup(String bucket, String name){
		Map<String, Object> map = null;
		try{
			map = getJdbcTemplate().queryForMap(SQL_SELECT_REST_GROUP, new Object[]{bucket, name});
		}catch (Exception e){
			LOG.debug("load rest usr acl error", e);
		}
		RestGroup restGroup = JsonUtils.mapToObj(map, RestGroup.class);
		return restGroup;
	}
	
	
	public List<RestRole> loadRestRoles(String bucket, String names){
		List<Map<String, Object>> maps = null;
		List<String> nameList = new ArrayList<String>();
		if(StringUtils.isNotEmpty(names)){
			nameList.addAll(Arrays.asList(names.split(",")));
		}
		StringBuffer sb = new StringBuffer();
		if(nameList.size() > 0){
			Object[] objs = new Object[nameList.size()+1];
			objs[0] = bucket;
			int index = 1;
			if(null != nameList){
				for(String name : nameList){
					if(StringUtils.isNotEmpty(name)){
						sb.append(" or `name` = ? ");
					}
					objs[index] = nameList.get(index - 1);
				}
				index++;
			}
			String sql = SQL_SELECT_REST_ROLE_LIST + sb.toString() + ")";
			try{
				maps = getJdbcTemplate().queryForList(sql, objs);
			}catch (Exception e){
				LOG.debug("load rest role error", e);
			}
		}else{
			String sql = SQL_SELECT_REST_ROLE_LIST + ")";
			try{
				maps = getJdbcTemplate().queryForList(sql, new Object[]{bucket});
			}catch (Exception e){
				LOG.debug("load rest role error", e);
			}
		}
		List<RestRole> list = new ArrayList<RestRole>();
		for(Map<String, Object> field:maps){
			RestRole restRole = JsonUtils.mapToObj(field, RestRole.class);
			list.add(restRole);
		}
		return list;
	}

	@Override
	public int insertRestRole(String bucket, String name, String desc) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_ROLE_INSERT, new Object[]{bucket, name, desc});
		}catch (Exception e){
			LOG.debug("insert rest role error", e);
		}
		return 0;
	}

	@Override
	public int deleteRestRole(String bucket, String name) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_ROLE_DELETE, new Object[]{bucket, name});
		}catch (Exception e){
			LOG.debug("delete rest role error", e);
		}
		return 0;
	}
	
	@Override
	public int deleteRestRole(int id) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_ROLE_DELETE_ID, new Object[]{id});
		}catch (Exception e){
			LOG.debug("delete rest role error", e);
		}
		return 0;
	}

	@Override
	public int insertRestGroup(String bucket, String name, String desc, String roles) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_GROUP_INSERT, new Object[]{bucket, name, desc, roles});
		}catch (Exception e){
			LOG.debug("insert rest group error", e);
		}
		return 0;
	}

	@Override
	public int updateRestGroup(String bucket, String name, String desc, String roles) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_GROUP_UPDATE, new Object[]{desc, roles, bucket, name});
		}catch (Exception e){
			LOG.debug("update rest group error", e);
		}
		return 0;
	}

	@Override
	public int deleteRestGroup(String bucket, String username) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_GROUP_DELETE, new Object[]{bucket, username});
		}catch (Exception e){
			LOG.debug("delete rest group error", e);
		}
		return 0;
	}
	
	@Override
	public int deleteRestGroup(int id) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_GROUP_DELETE_ID, new Object[]{id});
		}catch (Exception e){
			LOG.debug("delete rest group error", e);
		}
		return 0;
	}

	@Override
	public int insertRestUserAcl(String bucket, String username, String groups, String roles) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_USER_ACL_INSERT, new Object[]{bucket, username, groups, roles});
		}catch (Exception e){
			LOG.debug("insert rest user acl error", e);
		}
		return 0;
	}

	@Override
	public int updateRestUserAcl(String bucket, String username, String groups, String roles) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_USER_ACL_UPDATE, new Object[]{ groups, roles, bucket, username});
		}catch (Exception e){
			LOG.debug("update rest user acl error", e);
		}
		return 0;
	}

	@Override
	public int deleteRestUserAcl(String bucket, String username) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_USER_ACL_DELETE, new Object[]{bucket, username});
		}catch (Exception e){
			LOG.debug("delete rest user error", e);
		}
		return 0;
	}
	
	@Override
	public int deleteRestUserAcl(int id) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_USER_ACL_DELETE_ID, new Object[]{id});
		}catch (Exception e){
			LOG.debug("delete rest user error", e);
		}
		return 0;
	}

	@Override
	public int countRestUserAcl(String bucket, String username) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_USER_ACL_COUNT, new Object[]{bucket, username});
		}catch (Exception e){
			LOG.debug("delete rest group error", e);
		}
		return 0;
	}

	@Override
	public int countRestGroup(String bucket, String name) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_GROUP_COUNT, new Object[]{bucket, name});
		}catch (Exception e){
			LOG.debug("delete rest group error", e);
		}
		return 0;
	}

	@Override
	public List<String> loadRestRolesFromGroup(String bucket, String names) {
		List<String> result = new ArrayList<String>();
		List<RestGroup> groups = loadRestGroups(bucket, names);
		if(null != groups){
			for(RestGroup restGroup : groups){
				if(StringUtils.isNotEmpty(restGroup.getRoles())){
					result.addAll(Arrays.asList(restGroup.getRoles()));
				}
			}
		}
		return result;
	}
	
	
	public void createDataBase(String dbName, String user, String pwd){
		//创建数据库Create Database If Not Exists MyDB Character Set UTF8
		this.getJdbcTemplate().execute("create database if not exists " + dbName + " DEFAULT CHARACTER SET utf8 ;");
		//授权
		this.getJdbcTemplate().execute("grant all privileges on " + dbName + ".* to " + user + "@'%' identified by '" + pwd + "';");
	}

	@Override
	public List<Map<String, Object>> loadRestTables(String bucket) {
		//rest table
		List<Map<String, Object>> maps = null;
		try{
			maps = getJdbcTemplate().queryForList(SQL_SELECT_REST_TABLES, new Object[]{bucket});
		}catch (Exception e){
			LOG.debug("load rest table error", e);
		}
		return maps;
	}

	@Override
	public List<RestGroup> loadRestGroups(String bucket, String names) {
		List<RestGroup> result = new ArrayList<RestGroup>();
		List<String> nameList = new ArrayList<String>();
		if(StringUtils.isNotEmpty(names)){
			nameList.addAll(Arrays.asList(names.split(",")));
		}
		StringBuffer sb = new StringBuffer();
		List<Map<String, Object>> maps = null;
		if(nameList.size() > 0){
			Object[] objs = new Object[nameList.size()+1];
			objs[0] = bucket;
			int index = 1;
			if(null != nameList){
				for(String name : nameList){
					if(StringUtils.isNotEmpty(name)){
						sb.append(" or `name` = ? ");
					}
					objs[index] = nameList.get(index - 1);
				}
				index++;
			}
			String sql = SQL_SELECT_REST_GROUP_LIST + sb.toString() + ")";
			try{
				maps = getJdbcTemplate().queryForList(sql, objs);
			}catch (Exception e){
				LOG.debug("load rest admin error", e);
			}
		}else{
			String sql = SQL_SELECT_REST_GROUP_LIST + ")";
			try{
				maps = getJdbcTemplate().queryForList(sql, new Object[]{bucket});
			}catch (Exception e){
				LOG.debug("load rest admin error", e);
			}
		}
		if(null != maps){
			for(Map<String, Object> field:maps){
				RestGroup restGroup = JsonUtils.mapToObj(field, RestGroup.class);
				if(restGroup != null){
					result.add(restGroup);
				}
			}
		}
		return result;
	}

	@Override
	public RestRole loadRestRole(String bucket, String name) {
		Map<String, Object> map = null;
		try{
			map = getJdbcTemplate().queryForMap(SQL_SELECT_REST_ROLE, new Object[]{bucket, name});
		}catch (Exception e){
			LOG.debug("load rest usr acl error", e);
		}
		RestRole restRole = JsonUtils.mapToObj(map, RestRole.class);
		return restRole;
	}

	@Override
	public int updateRestRole(String bucket, String name, String desc) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_ROLE_UPDATE, new Object[]{desc, bucket, name});
		}catch (Exception e){
			LOG.debug("update rest role error", e);
		}
		return 0;
	}

	@Override
	public int insertRestUser(String bucket, String table, String nameField,
			String passField, String statusField, boolean emailAuth,
			String defaultGroup, String emailField, String mobileField) {
		try{
			return getJdbcTemplate().update(SQL_SELECT_REST_USER_INSERT, new Object[]{bucket,table,nameField,passField,statusField,emailAuth,defaultGroup,emailField,mobileField});
		}catch (Exception e){
			LOG.debug("insert rest user error", e);
		}
		return 0;
	}

	@Override
	public int updateRestUser(int id, String bucket, String table,
			String nameField, String passField, String statusField,
			boolean emailAuth, String defaultGroup, String emailField, String mobileField) {
		try{
			if(id>0){
				return getJdbcTemplate().update(SQL_SELECT_REST_USER_UPDATE_BY_ID, new Object[]{table,nameField,passField,statusField,emailAuth,defaultGroup,emailField,mobileField,id});
			}else if(StringUtils.isNotEmpty(bucket)){
				return getJdbcTemplate().update(SQL_SELECT_REST_USER_UPDATE_BY_ID, new Object[]{table,nameField,passField,statusField,emailAuth,defaultGroup,emailField,mobileField,bucket});
			}
		}catch (Exception e){
			LOG.debug("update rest user error", e);
		}
		return 0;
	}


}
